# coding=utf-8-sig
import transaction

from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm.exc import NoResultFound

from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy import Column
from sqlalchemy import String

from zope.sqlalchemy import ZopeTransactionExtension

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()

from pyramid.response import Response
from pyramid.view import view_config

@view_config(renderer='json')
def rate(request):
    return {'content':'ciao!'}

def getDescr(request):
    session= DBSession()
    query = session.query(Word).filter(Word.value == request.params['word'])
    word = query.one()
    return [word.value, word.type, word.descr]
    
def searchWord(context, request):
    print 'context: %s' % request.context
    print 'context arg: %s' % context
    session= DBSession()
    query = session.query(Word).filter(Word.value.like(request.params['term']+'%'))
    return [item.value for item in query.all()]


class Word(Base):
    __tablename__ = "dictionary"
    
    id = Column(Integer, primary_key=True)
    value = Column(Unicode(255))
    descr = Column(Unicode(255))
    type = Column(String(64))
    
    def __init__(self, word, type, descr):
        self.value = word
        self.type = type
        self.descr = descr


class MyApp(object):
    __name__ = None
    __parent__ = None

    def __getitem__(self, key):
        session= DBSession()
        try:
            id = int(key)
        except (ValueError, TypeError):
            raise KeyError(key)

        query = session.query(Word).filter_by(id=id)

        try:
            item = query.one()
            item.__parent__ = self
            item.__name__ = key
            return item
        except NoResultFound:
            raise KeyError(key)

    def get(self, key, default=None):
        try:
            item = self.__getitem__(key)
        except KeyError:
            item = default
        return item

    def __iter__(self):
	
        session= DBSession()
        query = session.query(Word)
        return iter(query)

root = MyApp()

def default_get_root(request):
    return root


def populate():
    
    session = DBSession()
    model = MyModel(name=u'test name', value=55)
    session.add(model)
    session.flush()
    transaction.commit()

def initialize_sql(engine):
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine
    Base.metadata.create_all(engine)
#    try:
#        populate()
#    except IntegrityError:
#        DBSession.rollback()

def appmaker(engine):
    initialize_sql(engine)
    return default_get_root

def populateWords():
    
    import os
    import xlrd
    from sqlalchemy import create_engine, engine_from_config

    print os.getcwd()

    # TODO: get the configuration from pyramid development.ini
    #engine = engine_from_config({'sqlalchemy.url': 'mysql+pymysql://etrentin:etrentin@localhost/etrentin', 'sqlalchemy.echo':True}, 'sqlalchemy.')
    engine = create_engine('mysql+mysqlconnector://root@localhost:3306/etrentin?charset=utf8', echo=False)
    initialize_sql(engine)

    book = xlrd.open_workbook("../docs/TRENTIN.xls")
    print "The number of worksheets is", book.nsheets
    print "Worksheet name(s):", book.sheet_names()

    session = DBSession()
    transaction.begin();

    idx = 0
    while idx < 21:
        sh = book.sheet_by_index(idx)
        print 'loading sheet %s' % sh.name
        #print "Cell D4 is", sh.cell_value(rowx=4, colx=2)
        for rx in range(1,sh.nrows):
            print '[%s] word: [%s]' % (rx, sh.row(rx)[0].value)
            if sh.row(rx)[0].value == '':
                break
            
            word = Word(sh.row(rx)[0].value, sh.row(rx)[1].value, sh.row(rx)[2].value)
            
            dbword = session.query(Word).filter_by(value=word.value).first()
          
            if dbword is None:
                print 'adding word= %s' % word.value
                session.add(word)
        idx += 1
  
    session.flush()
    transaction.commit()


if __name__ == "__main__":
    populateWords()
